package com.zhx.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zhx.database.DbUtil;
import com.zhx.menu.Menu;
import com.zhx.vo.Page;

/**
 * 菜单管理DAO，提供数据查询的方法
 * 
 * @author 演示
 *
 */
public class MenuDao extends BaseDao {

	@Override
	protected Menu handle(ResultSet rs) throws SQLException {
		Menu menu = new Menu();
		menu.setId(rs.getInt("id"));
		menu.setMenuId(rs.getString("menu_id"));
		menu.setMenuName(rs.getString("menu_name"));
		menu.setMenuFatherId(rs.getString("menu_father_id"));
		menu.setUrl(rs.getString("menu_url"));
		menu.setCreateTime(rs.getTimestamp("create_time"));
		menu.setUpdateTime(rs.getTimestamp("update_time"));
		return menu;

	}

	/**
	 * 分页
	 * 
	 * @param sql
	 * @param params
	 * @param page
	 * @param size
	 * @return
	 * @throws SQLException
	 */
	public Page queryOjectsByPage(String sql, Object[] params, int pageNo, int size) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Page page = null;
		List<Menu> datas = new ArrayList<Menu>();
		try {
			int start = (pageNo - 1) * size;
			conn = DbUtil.getConn();
			pstmt = conn.prepareStatement(sql + " limit " + start + "," + size);
			int index = 1;
			if (params != null) {
				for (Object param : params) {
					if (param instanceof String) {
						pstmt.setString(index++, (String) param);
					}
					if (param instanceof Integer) {
						pstmt.setInt(index++, (Integer) param);
					}
					if (param instanceof Float) {
						pstmt.setFloat(index++, (Float) param);
					}
				}
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Menu menu = handle(rs);
				datas.add(menu);
			}
			// 统计SQL对应记录数
			int total = count(sql, params);
			page = new Page(total, pageNo, size, datas);
			return page;
		} catch (SQLException e) {
			throw new SQLException("执行SQL[" + sql + "]失败", e);
		}

	}
	
	/**
	 *  添加菜单
	 * @param menu
	 * @throws SQLException
	 */
	public void add(Menu menu) throws SQLException {
		if (menu != null) {
			DbUtil dbUtil = new DbUtil();
			PreparedStatement pstmt = null;
			Connection conn = dbUtil.getConn();
			StringBuffer sqlBuff = new StringBuffer(
					"insert into sys_menu(menu_id, menu_name, menu_father_id, menu_url, create_time, update_time)");
			sqlBuff.append("values(?,?,?,?,?,?)");
			try {
				pstmt = conn.prepareStatement(sqlBuff.toString());
				pstmt.setString(1, menu.getMenuId());
				pstmt.setString(2, menu.getMenuName());
				pstmt.setString(3, menu.getMenuFatherId());
				pstmt.setString(4, menu.getUrl());
				pstmt.setTimestamp(5, menu.getCreateTime());
				pstmt.setTimestamp(6, menu.getUpdateTime());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbUtil.close(pstmt);
			}

		}
	}
	
	/**
	 * 根据ID获取
	 * @param menuId
	 * @return
	 * @throws SQLException
	 */
	public Menu getMenuByMenuId(String menuId) throws SQLException {
		Menu menu = new Menu();
		if(menuId != null) {
			DbUtil dbUtil = new DbUtil();
			
			String sql = "select * from sys_menu where menu_id = '"+ menuId +"'";
			ResultSet rs = dbUtil.executeQuery(sql);
			while (rs.next()) {
				menu = handle(rs);
			}
		}
		return menu;
	}
	
	/**
	 * 更新
	 * @param menu
	 * @throws SQLException
	 */
	public void updateMenu(Menu menu) throws SQLException {
		if (menu != null) {
			DbUtil dbUtil = new DbUtil();
			PreparedStatement pstmt = null;
			Connection conn = dbUtil.getConn();
			StringBuffer sqlBuff = new StringBuffer(
					"update sys_menu set menu_id = ?, menu_name = ?, menu_father_id = ?, menu_url = ?,  update_time = ? where id = ?");
			try {
				pstmt = conn.prepareStatement(sqlBuff.toString());
				pstmt.setString(1, menu.getMenuId());
				pstmt.setString(2, menu.getMenuName());
				pstmt.setString(3, menu.getMenuFatherId());
				pstmt.setString(4, menu.getUrl());
				pstmt.setTimestamp(5, menu.getUpdateTime());
				pstmt.setInt(6, menu.getId());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbUtil.close(pstmt);
			}

		}
	}
	
	/**
	 * 删除菜单信息
	 * @param menu
	 * @throws SQLException 
	 */
	public void deleteMenu(Menu menu) throws SQLException {
		if (menu != null) {
			DbUtil dbUtil = new DbUtil();
			PreparedStatement pstmt = null;
			Connection conn = dbUtil.getConn();
			StringBuffer sqlBuff = new StringBuffer(
					"delete from sys_menu where id = ?");
			try {
				pstmt = conn.prepareStatement(sqlBuff.toString());
				pstmt.setInt(1, menu.getId());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbUtil.close(pstmt);
			}

		}
	}
	
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Menu> queryOjects(String sql,Object[]params)throws SQLException{
		Connection conn =null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		List<Menu> results=new ArrayList<Menu>();
		try{
			conn=DbUtil.getConn();
			pstmt=conn.prepareStatement(sql);
			int index=1;
			if(params!=null){
			  for(Object param:params){
				  if(param instanceof String){
					  pstmt.setString(index++, (String)param);
				  }
				  if(param instanceof Integer){
					  pstmt.setInt(index++, (Integer)param);
				  }
				  if(param instanceof Float){
					  pstmt.setFloat(index++, (Float)param);
				  }
			  }
			}
			rs=pstmt.executeQuery();
			while(rs.next()){

				Menu menu=handle(rs);
				results.add(menu);
			
			}
			return results;
		}catch(SQLException e){
			throw new SQLException("执行SQL["+sql+"]失败",e);
		}
		
	}

}
